# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql

class ZhihuPipeline(object):
    def process_item(self, item, spider):
        #判断来自哪个spider来确定调用哪个pipline
        if spider.name=='lg':
            if item['textType']==0:
                try:
                    #连接中心节点的数据库，写入问答类型的信息
                    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', db='zhihu_question', use_unicode=True, charset="gbk")
                    with conn.cursor() as cur:
                        cur.execute('SET NAMES gbk')
                        cur.execute('SET CHARACTER SET gbk')
                        cur.execute('SET character_set_connection=gbk')
                        cur.execute('show tables')
                        tableName=cur.fetchall()
                        is_in_dd=False
                        if len(tableName) != 0:
                            for k in tableName:
                                if '_'+item['tag1']==k[0]:
                                    is_in_dd=True
                                    break
                        if not is_in_dd:
                            cur.execute('create table _'+item['tag1']+'(id int(4) primary key auto_increment,tag2 char(50),questionTitle char(100),questionUrl char(50),state int(1))')
                        sql = "insert into _"+item['tag1']+"(tag2,questionTitle,questionUrl,state) values('" + item['tag2'] + "','" + item['quesTitle'] + "','" + item['questionUrl'] +"','" + str(0)+ "')"
                        cur.execute(sql)
                        conn.close()
                except Exception as err:
                        print(err)
            else:
                try:
                    #连接中心节点，写入文章类型的信息
                    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', db='zhihu_text',use_unicode=True, charset="gbk")
                    with conn.cursor() as cur:
                        cur.execute('SET NAMES gbk')
                        cur.execute('SET CHARACTER SET gbk')
                        cur.execute('SET character_set_connection=gbk')
                        cur.execute('show tables')
                        tableName=cur.fetchall()
                        is_in_dd=False
                        if len(tableName) != 0:
                            for k in tableName:
                                if '_'+item['tag1']==k[0]:
                                    is_in_dd=True
                                    break
                        if not is_in_dd:
                            cur.execute('create table _'+item['tag1']+'(id int(4) primary key auto_increment,tag2 char(30),questionTitle char(100),questionUrl char(50),state int(1))')
                        sql = "insert into _"+item['tag1']+"(tag2,questionTitle,questionUrl,state) values('" + item['tag2'] + "','" + item['quesTitle'] + "','" + item['questionUrl'] +"','" + str(0)+ "')"
                        cur.execute(sql)
                        conn.close()
                except Exception as err:
                        print(err)
            return item

        #处理content spider的item，爬取具体的数据
        if spider.name=='content':
            if str(item.__class__)=="<class 'zhihu.items.questionItem'>":
                print('p1')
                try:
                    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', db='zhihu_questionans', use_unicode=True, charset="gbk")
                    with conn.cursor() as cur:
                        cur.execute('SET NAMES gbk')
                        cur.execute('SET CHARACTER SET gbk')
                        cur.execute('SET character_set_connection=gbk')
                        cur.execute('show tables')
                        tableName=cur.fetchall()
                        is_in_dd=False
                        if len(tableName)!=0:
                            for k in tableName:
                                if '_'+item['quesID']==k[0]:
                                    is_in_dd=True
                                    break
                        if not is_in_dd:
                            cur.execute('create table _'+item['quesID']+'(id int(4) primary key auto_increment,tag char(30),questionTitle char(50),questionDis char(100),ansContent varchar(1500),ansAuthor char(30))')
                        sql = "insert into _"+item['quesID']+"(tag,questionTitle,questionDis,ansContent,ansAuthor) values('" + item['tag'] + "','" + item['title'] + "','" + item['titleDis'] +"','" + str(item['ansContent'])+"','" + item['ansAuthor']+"')"
                        cur.execute(sql)
                        conn.close()
                except Exception as err:
                        print(err)

            if str(item.__class__)=="<class 'zhihu.items.textItem'>":
                print('p2')
                try:
                    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', db='zhihu_textcon',use_unicode=True, charset="gbk")
                    with conn.cursor() as cur:
                        cur.execute('SET NAMES gbk')
                        cur.execute('SET CHARACTER SET gbk')
                        cur.execute('SET character_set_connection=gbk')
                        cur.execute('show tables')
                        tableName = cur.fetchall()
                        is_in_dd = False
                        if len(tableName) != 0:
                            for k in tableName:
                                if '_textcontent' == k[0]:
                                    is_in_dd = True
                                    break
                        if not is_in_dd:
                            cur.execute('create table _textcontent(id int(4) primary key auto_increment,tag char(30),title char(50),author char(30),content varchar(3000))')
                        sql = "insert into _textcontent(tag,title,author,content) values('"+item['tag']+ "','"+item['title'] +"','"+item['author']+"','"+ item['content'] + "')"
                        cur.execute(sql)
                        conn.close()
                except Exception as err:
                    print(err)
